package com.qs.commontools.utils.file;


import com.qs.commontools.constants.FileConstant;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Excel文件的通用方法
 * @author qiusuo
 * @since 2021-04-15
 */
public class ExcelUtils {


    private static HSSFWorkbook hWorkbook = null;
    private static XSSFWorkbook xWorkbook = null;

    /**
     * 解析单元格数据的方法。
     * 该方法能够解析所有的单元格对象，并以字符串的形式返回：
     * <ul>
     *     <li>1、假如单元格为null或者为""则返回""</li>
     *     <li>2、假如单元格为正常字符串则返回字符串</li>
     *     <li>3、假如单元格可能为时间格式如 2021-04/12、2021-3.12等格式会自动转换成yyyy-MM-dd的时间字符串格式</li>
     *     <li>4、假如是2021-02/43，2021年-02-92等异常形式会尽量转换成yyyy-MM-dd的时间字符串形式，但整体基本不变</li>
     *     <li>5、假如原本单元格就是时间格式如2021/7/31 cellValue会被解释成5位数字类型也可对其进行筛选</li>
     * </ul>
     *
     * @param cell 单元格对象
     * @return 单元格数据返回值都是字符串，
     */
    public static String getCellValue(Cell cell) {
        String cellValue = FileConstant.STR_EMPTY;
        //判断是否为null或空串，都返回空字符串
        if (cell == null || cell.toString().trim().equals(FileConstant.STR_EMPTY)) {
            return FileConstant.STR_EMPTY;
        }
        //获取该单元格类型
        int cellType = cell.getCellType();
        //把数字当成String来读，避免出现1读成1.0的情况
//        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
//            cell.setCellType(Cell.CELL_TYPE_STRING);
//        }
        //判断数据的类型
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                //数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    }else {
                        // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        // 日期
                        cellValue = sdf.format(cell.getDateCellValue());
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                }  else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    // 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                    cellValue = bd.toPlainString();
                }
//                cellValue = String.valueOf(cell.getNumericCellValue())
                break;
            case Cell.CELL_TYPE_STRING:
                //字符串
                //调用字符串判断方法
                cellValue = judgeStringDate(String.valueOf(cell.getStringCellValue()));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                //公式
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                //空值
                cellValue = FileConstant.STR_EMPTY;
                break;
            case Cell.CELL_TYPE_ERROR:
                //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 判断字符串是否是日期类型。
     *
     * @param value 需要校验的字符串
     * @return 将值转为日期字符串结果
     */
    public static String judgeStringDate(String value){
        try{
            //是否为存数字
            int num = Integer.valueOf(value);
            //既可以转为数字，又为五位数
            if (value.length() == 5){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                Date tDate = new Date();
                //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
                long localOffset = tDate.getTimezoneOffset() * 60000;
                tDate.setTime((long) ((Double.parseDouble(value) - 25569) * 24 * 3600 * 1000 + localOffset));
                //至此将poi包解析的日期类型转为字符串类型
                value = sdf.format(tDate);
            }
        }catch (Exception e){
            e.printStackTrace();
            //报异常就不是数字

        }

        String result = value;

        String pattern1 = "^\\d{4}(年|-|/|\\.|\\\\|\\*)(0?[1-9]|1[0-2])(月|-|/|\\.|\\\\|\\*)((0?[1-9])|((1|2)[0-9])|30|31)(日|)$";
        String pattern2 = "^\\d{4}(0?[1-9]|1[0-2])((0?[1-9])|((1|2)[0-9])|30|31)$";

        Pattern r1 = Pattern.compile(pattern1);
        Matcher m1 = r1.matcher(value);
        Pattern r2 = Pattern.compile(pattern2);
        Matcher m2 = r2.matcher(value);
        if(m1.matches()){
            result = value.replaceAll("(年|月|-|/|\\.|\\\\|\\*)","-");
            result = result.replaceAll("日","");
        }else if(m2.matches()){
            if(value.length() != 8){
                return result;
            }
            result = value.substring(0,4) + "-" + value.substring(4,6) + "-" + value.substring(6);
        }else{
            return result;
        }

        String[] dateArr = result.split("-");
        int year = Integer.valueOf(dateArr[0]);
        int month = Integer.valueOf(dateArr[1]);
        int day = Integer.valueOf(dateArr[2]);
        int[] monthLengths = new int[]{0, 31, -1, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
        // 是否是闰年
        boolean isleap = (year % 4 == 0 && year % 100 != 0) || year % 400 == 0;
        if(isleap){
            monthLengths[2] = 29;
        }else{
            monthLengths[2] = 28;
        }
        int monthLength = monthLengths[month];
        if(day>monthLength){
            return result;
        }

        result = year + "-" + ("0" + month).substring(("0" + month).length() - 2) + "-" + ("0" + day).substring(("0" + day).length() - 2);

        return result;
    }


    /**
     * 根据文件file获取Excel整个文件的操作对象。
     *
     * @param file MultipartFile类型的Excel文件
     * @throws IOException 获取Excel操作对象异常
     * @return Excel文件操作对象
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;

        //获取excel文件的io流
        InputStream is = file.getInputStream();
        //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
        if (fileName.endsWith(FileConstant.FILE_SUFFIX_XLS)) {
            //2003
            workbook = new HSSFWorkbook(is);
        } else if (fileName.endsWith(FileConstant.FILE_SUFFIX_XLSX)) {
            //2007
            workbook = new XSSFWorkbook(is);
        }

        return workbook;
    }

    /**
     * 根据文件file获取Excel整个文件的操作对象。
     *
     * @param file File类型的Excel文件
     * @throws IOException 获取Excel操作对象异常
     * @return Excel文件操作对象
     */
    public static Workbook getWorkBook(File file) throws IOException {
        MultipartFile multipartFile = null;
        try {
             multipartFile = FileUtils.getMultipartFile(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return getWorkBook(multipartFile);
    }


    /**
     * 判断文件的sheet是否存在。
     *
     * @param fileDir   文件路径
     * @param sheetName  表格索引名
     * @return true存在，false不存在
     */
    public static boolean sheetExist(String fileDir, String sheetName){

        boolean flag = false;
        File file = new File(fileDir);

        if (file.exists()) {
            //文件存在，创建workbook
            try {
                hWorkbook = new HSSFWorkbook(new FileInputStream(file));

                HSSFSheet sheet = hWorkbook.getSheet(sheetName);
                if (sheet!=null) {
                    //文件存在，sheet存在

                    flag = true;
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else {
            //文件不存在
            flag = false;
        }
        return flag;
    }


    /**
     * 创建新excel(xls)文件。
     * @param fileDir excel的路径
     * @param sheetNames 要创建的表格索引列表
     * @param titleRow  excel的第一行即表格头
     */
    public static void createExcelXls(String fileDir, List<String> sheetNames, String titleRow[]){

        //创建workbook
        hWorkbook = new HSSFWorkbook();
        //新建文件
        FileOutputStream fileOutputStream = null;
        HSSFRow row = null;
        try {

            CellStyle cellStyle = hWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

            //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
            for(int i = 0; i<sheetNames.size(); i++){
                hWorkbook.createSheet(sheetNames.get(i));
                hWorkbook.getSheet(sheetNames.get(i)).createRow(0);
                //添加表头, 创建第一行
                row = hWorkbook.getSheet(sheetNames.get(i)).createRow(0);
                row.setHeight((short)(20*20));
                for (short j = 0; j < titleRow.length; j++) {

                    HSSFCell cell = row.createCell(j, CellType.BLANK);
                    cell.setCellValue(titleRow[j]);
                    cell.setCellStyle(cellStyle);
                }
                fileOutputStream = new FileOutputStream(fileDir);
                hWorkbook.write(fileOutputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 往excel(xls)中写入(已存在的数据无法写入)。
     * @param fileDir    文件路径,写入文件的路径
     * @param sheetName  表格索引，写入sheet工作页的名字
     * @param mapList   数据集合
     * @throws Exception 写入异常
     */

    public static void write(String fileDir, String sheetName, List<Map<String,String>> mapList) throws Exception{

        //创建workbook
        File file = new File(fileDir);

        try {
            hWorkbook = new HSSFWorkbook(new FileInputStream(file));
        }catch (Exception e) {
            e.printStackTrace();
        }

        //文件流
        FileOutputStream fileOutputStream = null;
        HSSFSheet sheet = hWorkbook.getSheet(sheetName);
        // 获取表格的总行数
        // int rowCount = sheet.getLastRowNum() + 1; // 需要加一
        //获取表头的列数
        int columnCount = sheet.getRow(0).getLastCellNum();

        try {
            // 获得表头行对象
            HSSFRow titleRow = sheet.getRow(0);
            //创建单元格显示样式
            CellStyle cellStyle = hWorkbook.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);


            if(titleRow!=null){
                for(int rowId = 0; rowId < mapList.size(); rowId++){
                    Map<String,String> map = mapList.get(rowId);
                    HSSFRow newRow=sheet.createRow(rowId+1);
                    //设置行高  基数为20
                    newRow.setHeight((short)(20*20));
                    //遍历表头
                    for (short columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                        //trim()的方法是删除字符串中首尾的空格
                        String mapKey = titleRow.getCell(columnIndex).toString().trim();
                        HSSFCell cell = newRow.createCell(columnIndex);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(map.get(mapKey)==null ? null : map.get(mapKey));
                    }
                }
            }

            fileOutputStream = new FileOutputStream(fileDir);
            hWorkbook.write(fileOutputStream);
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}
